Imports System.Threading
Imports Crystal.DatabaseManagement.BusinessLayer
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Data.SqlClient
Imports Crystal.CommonDBManagement
Imports CrystalCommon.View
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class frmUploadInvestor

#Region "Declare"
    'Private _MeHeightWithStat As Integer = 0
    'Private _MeHeight As Integer = 0
    'Private _MeHeightWithoutStat As Integer = 0
    'Private _MeHeightWithNotImported As Integer = 0
    Private _IsReRun As Boolean
    Private _SheetID As Integer
    Private _TotalExcelRows As Integer
    Private _IsChkSheet As Boolean
    Private _IsThreadAborted As Boolean
    Private _SheetName As String
    Private _FormatID As String
    Private _Thread As Thread
    Private connectionString As String = CommonCLL.mstrConnectionstring
    Public WithEvents _InvestorFormat As frmTemplateMapping
    Dim WithEvents CInvestorDetails As New cInvestorDetails

    Delegate Sub GenricDelegate()
    Delegate Sub IncrementBySteps(ByVal pintStep As Integer)
    Delegate Sub dlShowValidation(ByVal ldtValidationTypes As DataTable, ByVal DuplicateRecord As DataTable)
    Delegate Sub ControlVisiblity(ByVal isVisible As Boolean)
    Delegate Sub ShowState()
    Public Event LoadImportedInvestor(ByVal dtExce As DataTable)
    'Private HerightOnload As Integer

#End Region

#Region "On Load"

    Private Sub frmUploadInvestor_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If IsChildFormOpened() = False Then
            e.Cancel = True
        End If
    End Sub

    Private Sub frmImportHistory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try

            InitControls()
            LoadControls()
            'Me.WindowState = FormWindowState.Normal
            'HerightOnload = Me.Height
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in frmImportHistory_Load of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            'frmInvImp = New frmImportInvestor
        End Try
    End Sub

    Private Function IsChildFormOpened() As Boolean
        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
            If Application.OpenForms(lmFomrs).Name = "frmInvestorUploadValidations" Then
                Application.OpenForms(lmFomrs).BringToFront()
                Return False
            End If
        Next
        Return True
    End Function

#Region "Init"

    ''' <summary>
    ''' To Initilize the controls on load
    ''' </summary>
    ''' <remarks></remarks> 
    Private Sub InitControls()
        Try
            InitCombos()
            SetControls()
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    ''' <summary>
    ''' To Initilize the Combo Boxes on load
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub InitCombos()
        With cmbSheets
            .ValueMember = "SNo."
            .DisplayMember = "Name"
        End With

        With cmbSourceName
            .ValueMember = "SourceID"
            .DisplayMember = "Source Name"
        End With

        With cmbTemplate
            .ValueMember = "TemplateID"
            .DisplayMember = "Template"
        End With

    End Sub

    ''' <summary>
    ''' To Set various properties(Visibility, Enableness) of controls on load
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetControls()
        Try
            cmbSheets.Enabled = False
            'gpbStats.Visible = False
            lblInProgress.Visible = False
            ' _MeHeight = Me.Height
            '_MeHeightWithNotImported = Me.Height - grdNotImported.Height - lblNotImported.Height
            ' _MeHeightWithStat = Me.Height - grdNotImported.Height - lblNotImported.Height
            ' _MeHeightWithoutStat = Me.Height - gpbStats.Height - grdNotImported.Height - 10
            ' Me.Height = _MeHeightWithoutStat + 30

            'grdNotImported.Visible = False
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

#End Region

#Region "Load"

    ''' <summary>
    ''' To load the controls on load
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadControls()
        Try
            LoadSourceNames()
            LoadTemplate()
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    ''' <summary>
    ''' To load the Template Combo Box
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadSourceNames()
        Dim eReturnVal As New ExecutionResult
        Dim dtSourceNames As New DataTable
        Dim clscImportInvestor As New cImportInvestors
        Dim IDR As IDataReader = Nothing
        Try
            'clscImportInvestor.TemplateID = -1
            clscImportInvestor.SourceTypeCode = SourceTypeCode.INV.ToString()
            IDR = clscImportInvestor.SelectSourceNames
            dtSourceNames.Load(IDR)
            mdlCommon.ChangeDataReaderDataTable(dtSourceNames)
            'dtFormat = CType(eReturnVal.ResultObject, DataSet).Tables(0)
            If BLLmdlCommon.CheckNothingDataTable(dtSourceNames) Then Exit Sub
            Dim dr As DataRow
            dr = dtSourceNames.NewRow
            dr.Item("SourceID") = -1
            dr.Item("Source Name") = m_strSelectTextValue
            dtSourceNames.Rows.InsertAt(dr, 0)
            cmbSourceName.DataSource = dtSourceNames
            btnSourceName.Visible = False

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in LoadSourceNames of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            eReturnVal = Nothing
            dtSourceNames = Nothing
            IDR = Nothing
        End Try
        'mObjImportInvestor = Nothing
    End Sub

    ''' <summary>
    ''' To load the Template Combo Box
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadTemplate()
        If cmbSourceName.SelectedValue <= 0 Then Exit Sub
        Dim eReturnVal As New ExecutionResult
        Dim dtTemplate As New DataTable
        Dim clscImportInvestor As New cImportInvestors
        Dim IDR As IDataReader = Nothing
        Try
            'clscImportInvestor.TemplateID = -1
            clscImportInvestor.ESourceID = cmbSourceName.SelectedValue
            IDR = clscImportInvestor.SelectImportTemplates
            dtTemplate.Load(IDR)
            mdlCommon.ChangeDataReaderDataTable(dtTemplate)
            'dtFormat = CType(eReturnVal.ResultObject, DataSet).Tables(0)
            If BLLmdlCommon.CheckNothingDataTable(dtTemplate) Then Exit Sub
            Dim dr As DataRow
            dr = dtTemplate.NewRow
            dr.Item("TemplateID") = -1
            dr.Item("Template") = m_strSelectTextValue
            dtTemplate.Rows.InsertAt(dr, 0)
            cmbTemplate.DataSource = dtTemplate

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in LoadTemplate of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            eReturnVal = Nothing
            dtTemplate = Nothing
            IDR = Nothing
        End Try
        'mObjImportInvestor = Nothing
    End Sub

#End Region

#End Region

#Region "Events"

#Region "Form Events"

    Private Sub frmImportInvestor_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        Try
            _IsThreadAborted = True
            If Not _Thread Is Nothing Then
                If _Thread.IsAlive Then
                    If _Thread.ThreadState = 68 Then
                        _Thread.Resume()
                    End If
                    '_Thread.Abort()
                    '_Thread.Join()
                End If
            End If
            _IsThreadAborted = True
            _Thread = Nothing
            For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
                If Application.OpenForms(lmFomrs).Name = "frmInvestorManagement" Then
                    RemoveHandler LoadImportedInvestor, AddressOf CType(Application.OpenForms(lmFomrs), frmInvestorManagement).LoadImportedInvestor
                    RemoveHandler CInvestorDetails.EntInvRecordAdded, AddressOf IncrementProgressBarValue
                    CInvestorDetails = Nothing
                End If
            Next
            Me.Dispose()
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in frmImportInvestor_FormClosed of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            'frmInvImp = Nothing
        End Try
    End Sub

    Private Sub frmImportInvestor_Resize(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Resize
        For lmFomrs As Integer = 0 To Application.OpenForms.Count - 1
            If Application.OpenForms(lmFomrs).Name = "frmImportInvestor" Then
                If (Me.WindowState = FormWindowState.Normal) Then
                    CType(Application.OpenForms(lmFomrs), frmUploadInvestor).WindowState = Me.WindowState
                End If
                If (Me.WindowState = FormWindowState.Maximized) Then
                    CType(Application.OpenForms(lmFomrs), frmUploadInvestor).WindowState = Me.WindowState
                End If
            End If
        Next
    End Sub

#End Region

#Region "Button Events"

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Try
            'If (Not CInvestorDetails.trans Is Nothing) Then
            '    CInvestorDetails.trans.Rollback()
            'End If

            _IsThreadAborted = True
            ClearAfterImportControls()
            If Not _Thread Is Nothing Then

                If _Thread.IsAlive Then
                    If _Thread.ThreadState = 68 Then
                        _Thread.Resume()
                    End If
                    _Thread.Abort()
                    _Thread = Nothing
                    '      _Thread.Join()
                End If
            End If
            SetContolsOnImportClick(False)
            'btnRun.Enabled = True
        Catch ex1 As ThreadAbortException

        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnCancel_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try

    End Sub

    Private Sub btnClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Try
            'Me.WindowState = FormWindowState.Normal
            ClearAllFields()
            ClearAfterImportControls()
            btnClear.Enabled = False
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Private Sub btnContinue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnContinue.Click
        Try
            _Thread.Resume()
            btnPause.Enabled = True
            btnContinue.Enabled = False
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnContinue_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try

    End Sub

    Private Sub btnPause_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPause.Click
        Try
            _Thread.Suspend()
            btnPause.Enabled = False
            btnContinue.Enabled = True
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnPause_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try


    End Sub

    ''' <summary>
    ''' Close the window
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    ''' <summary>
    '''  'To Jump In Investor Format for Creating New Format or Changes with existing Format
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnTemplate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemplate.Click
        Dim IsColumDupliacte As Boolean = False
        Dim ExcelColumns As New DataTable
        Dim Id As String = String.Empty
        Dim mTemplateID As Integer = 0
        Dim Parent As String = String.Empty
        Try
            _SheetName = CType(cmbSheets.SelectedItem, DataRowView).Item("Name")
            If Not GetExcelFileData(IsColumDupliacte, True, ExcelColumns, txtAttach.Text, _SheetName, _TotalExcelRows) Then
                If IsColumDupliacte = False Then
                    MessageBox.Show("Please select correct file to import investor data", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
                Exit Sub
            End If
            Dim uc As New UCTemplateMapping(False, BLLmdlCommon.strConfiguration)
            uc.SourceID = cmbSourceName.SelectedValue
            uc.FormatID = cmbTemplate.SelectedValue
            mTemplateID = uc.FormatID
            uc.enmSourceTypeCode = SourceTypeCode.INV
            uc.mdtExcelClone = ExcelColumns

            _InvestorFormat = New frmTemplateMapping()
            AddHandler _InvestorFormat.ReloadFormatId, AddressOf Reload
            With _InvestorFormat
                .StartPosition = FormStartPosition.CenterScreen
                .Controls.Add(uc)
                .Width = uc.Width + 10
                .Height = uc.Height + 30
                .ShowDialog()
            End With
            If mTemplateID <> uc.FormatID Then
                Dim table As DataTable = cmbTemplate.DataSource
                If table.Columns.Contains("TemplateID") Then
                    Dim FilterQuery As String
                    Dim Excel() As DataRow
                    FilterQuery = "TemplateID ='" + Trim(uc.FormatID).Replace("'", "''") + "'"
                    Excel = table.Select(FilterQuery)
                    If Excel.Length = 0 Then
                        LoadTemplate()
                    End If
                    cmbTemplate.SelectedValue = uc.FormatID

                End If
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnFormat_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ExcelColumns = Nothing
        End Try
    End Sub

    Private Sub btnreimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReRun.Click
        _IsReRun = True
        _SheetID = cmbSheets.SelectedIndex
        btnBrowse_Click(sender, e)
        Import()
        btnReRun.Enabled = False
    End Sub

#End Region

#Region "Grid Event"

    ''' <summary>
    ''' assign the text to label control on grid click
    ''' </summary>
    ''' <remarks></remarks>
    Public Function GridClick(ByVal lintMessage As Integer) As String
        'ElseIf lintMessage = BLLmdlCommon.GridRecord.Numericstate Then
        '    Return "State name cannot be numeric. "
        'If grdNotImported.ActiveRow Is Nothing Then Exit Sub
        'If lintMessage Is DBNull.Value Then Exit Sub
        If lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInFile Then
            Return "Multiple instances of investor ID in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.MultipleInstancesInvestorName Then
            Return "Multiple instances of investor name in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInDB Then
            Return "Investor ID already exists in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.NumericInvestorName Then
            Return "Invalid Investor Name - Numeric names not allowed. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameBlank Then
            Return "Blank Investor Id and Investor Name."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.EntityBlank Then
            Return "EntityID cannot be blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Regionblank Then
            Return "Region cannot be blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.RegionNotExists Then
            Return "Region does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeFundExists Then
            Return "Investor type with 'Fund' or 'Market Ltd Part' cannot be imported."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DateNotWellFormated Then
            Return "Date not well formated. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvIDDifferAgainstInvName Then
            Return "Investor Name already exist for some other investor."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StatusNotExists Then
            Return "Invalid Status."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameExistsInFile Then
            Return "Investor Name exists multiple times."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameExistsInDB Then
            Return "Investor name already exists in the DB but with a different investor ID."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeBlank Then
            Return "Blank Investor Type."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeNotExistsInMDB Then
            Return "Invalid Investor Type - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.MasterInvestorTypeBlank Then
            Return "Please provide Master Investor Type in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeLinkingNotExists Then
            Return "Invalid link between Investor Type and Master Investor Type. Please either add the new link via the master tables or change to a valid link in the source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleBlank Then
            Return "Blank Style."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleNotExists Then
            Return "Invalid Style - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.ActivistNotExist Then
            Return "Invalid Activist."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.MasterInvestorNotExistinDB Then
            Return "Invalid Master Investor Type. Please provide valid Master Investor Type in source file - refer to master tables for a valid set of source types."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverNotExistsInDB Then
            Return "Invalid Turnover - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverBlank Then
            Return "Blank Turnover."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CityBlank Then
            Return "Blank City."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CityNotExist Then
            Return "Invalid City - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CountryCodeValid Then
            Return "Please provide valid country code."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Countryblank Then
            Return "Blank Country."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CountryNotExists Then
            Return "Invalid Country - Please either add to master tables or change in source file."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StateNotExists Then
            Return "Invalid State - Please provide two alpha characters only in source file. See master tables for valid list of states."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StateLengthNot2 Then
            Return "Invalid State - Please provide two alpha characters only in source file. See master tables for valid list of states."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StateBlank Then
            Return "Blank State."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.UnrecognisedInvestor Then
            Return "Unrecognised Investor ID and Investor Name Missing."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidCity Then
            Return "Invalid City - City already exist with other country."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidState Then
            Return "Invalid State - State already exist with other country."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DeleteStatusOfNewInvestor Then
            Return "New investor with 'Delete' status cannot be imported."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DeleteStatusCanntUpdate Then
            Return "Investor with 'Delete' status cannot be updated."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidNameCharacter Then
            Return "Please provide valid investor name."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvalidLinkCityCountry Then
            Return "Invalid link between City and Country."
        End If
        Return ""
    End Function

#End Region

#Region "Combo Events"
    Private Sub cmbSourceName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSourceName.SelectedIndexChanged
        Try
            btnRun.Enabled = False
            LoadTemplate()
            If cmbSourceName.SelectedIndex > 0 Then
                If DirectCast(cmbTemplate.DataSource, DataTable).Rows.Count > 1 Then
                    cmbTemplate.SelectedIndex = 1
                Else
                    cmbTemplate.SelectedIndex = 0
                End If
                btnClear.Enabled = True
                If _IsChkSheet = True Then
                    btnRun.Enabled = True
                End If
            Else
                If cmbTemplate.DataSource IsNot Nothing Then
                    cmbTemplate.SelectedIndex = 0
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbFormat_SelectedIndexChanged of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub
#End Region

#End Region

#Region "Browse Import File"

    Private Sub HideControls()
        'lblReseaon.Visible = False
        'grdNotImported.Visible = False
        'lblNotImported.Visible = False
    End Sub

    ''' <summary>
    ''' To Select Investor Excel file to Import
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        Dim ObjImportFile As New OpenFileDialog
        Try
            If _IsReRun Then
                GetWorksheetNames(Trim(txtAttach.Text))
            Else
                HideControls()
                With ObjImportFile
                    '.Filter = "Excel (97-2003 format)(*.xls)|*.xls|Excel (2007-2010 format)(*.xlsx)|*.xlsx"
                    .Filter = "Excel (2007-2010 format)(*.xlsx)|*.xlsx|Excel (97-2003 format)(*.xls)|*.xls"
                    .Multiselect = False
                    '.InitialDirectory = "C:\Users\Parminder\Documents\Institutions DB"
                    .InitialDirectory = "C:\"
                    '.InitialDirectory = "D:\Parminder\Projects\Crystal\Test Data\Investor Import"
                    .DefaultExt = ".xlsx"
                    If .ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
                        txtAttach.Text = .FileName
                        Application.DoEvents()
                        Try
                            If (Not CommonCLL.IsEmpty(txtAttach.Text)) AndAlso IO.File.Exists(Trim(txtAttach.Text)) Then
                                InitCombos()
                                If GetWorksheetNames(Trim(txtAttach.Text)) Then
                                    cmbSheets.Enabled = True
                                Else
                                    MessageBox.Show("Please select a valid file.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                                    Exit Sub
                                End If
                            End If
                        Catch ex As Exception
                            MsgBox(ex.Message)
                            '''''Logging
                            Dim Logmessage As New System.Text.StringBuilder
                            Logmessage.Append("There is error in btnBrowse_Click of form import investor.")
                            Logmessage.Append(Environment.NewLine)
                            Logmessage.Append("Error Message - ")
                            Logmessage.Append(ex.ToString())
                            CreateErrorLog(Logmessage)
                            Logmessage = Nothing
                            ''''''''''''''''''''''''''''
                        End Try
                    End If
                End With
                'btnImport.Enabled = False
                btnReRun.Enabled = False
                '***********************************************
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnBrowse_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ObjImportFile = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' 'To Select Sheet Names of the selected Investor Excel file
    ''' </summary>
    ''' <param name="File"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Private Function GetWorksheetNames(ByVal file As String) As Boolean
        Dim ReturnVal As Boolean = False
        Dim ExcelHandling As New ExcelHandling
        Dim WorkBooks As New DataTable

        Try

            'WorkBooks = ExcelHandling.GetWorksheetNames(file)
            Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
            internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(txtAttach.Text)
            Dim WorkBook As DataRow
            WorkBooks.Columns.Add("SNo.", GetType(Integer))
            WorkBooks.Columns.Add("Name", GetType(String))
            WorkBook = WorkBooks.NewRow()
            WorkBook("SNo.") = -1
            WorkBook("Name") = "---Select---"
            'WorkBook("Name") = ContentManager.GetContent("SELECT")
            WorkBooks.Rows.InsertAt(WorkBook, 0)
            If internalWorkBook.Worksheets.Count > 0 Then
                For i As Integer = 0 To internalWorkBook.Worksheets.Count - 1

                    WorkBook = WorkBooks.NewRow()
                    WorkBook("SNo.") = i + 1
                    WorkBook("Name") = internalWorkBook.Worksheets(i).Name
                    WorkBooks.Rows.Add(WorkBook)
                    WorkBook = WorkBooks.NewRow()
                Next
            End If
            internalWorkBook = Nothing
            If CommonCLL.CheckEmptyDataTable(WorkBooks) Then
                ReturnVal = False
            Else
                cmbSheets.Enabled = True
                cmbSheets.DataSource = WorkBooks
                If _IsReRun Then
                    cmbSheets.SelectedIndex = _SheetID
                Else
                    If WorkBooks.Rows.Count > 0 Then
                        cmbSheets.SelectedIndex = 1
                    End If
                End If
                ReturnVal = True
            End If

            btnRun.Enabled = True
        Catch ex As Exception
            MsgBox(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in GetWorksheetNames of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
            ReturnVal = False
        Finally
            ExcelHandling = Nothing
            WorkBooks = Nothing
        End Try
        Return ReturnVal
    End Function

    'Public Function GetWorksheetNames(ByVal File As String) As DataTable
    '    Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
    '    internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(txtAttach.Text)
    '    Dim WorkBooks As New DataTable()
    '    Dim WorkBook As DataRow

    '    WorkBooks.Columns.Add("SNo.", Type.[GetType]("System.Int32"))
    '    WorkBooks.Columns.Add("Name", Type.[GetType]("System.String"))
    '    WorkBook = WorkBooks.NewRow()
    '    WorkBook("SNo.") = -1
    '    WorkBook("Name") = ContentManager.GetContent("SELECT")
    '    WorkBooks.Rows.InsertAt(WorkBook, 0)
    '    If internalWorkBook.Worksheets.Count > 0 Then
    '        For i As Integer = 0 To internalWorkBook.Worksheets.Count - 1

    '            WorkBook = WorkBooks.NewRow()
    '            WorkBook("SNo.") = i
    '            WorkBook("Name") = internalWorkBook.Worksheets(i).Name
    '            WorkBooks.Rows.Add(WorkBook)
    '            WorkBook = WorkBooks.NewRow()
    '        Next
    '    End If
    '    internalWorkBook = Nothing
    '    Return (WorkBooks)

    '    'Catch ex As Exception
    '    '    Return Nothing
    '    'Finally
    '    '    XLApp = Nothing
    '    '    ExcWorkbook = Nothing
    '    '    WorkBooks = Nothing
    '    '    WorkBook = Nothing
    '    '    SheetNames = Nothing
    '    '    'WorkBook = Nothing
    '    'End Try
    'End Function

#End Region

#Region "Import Investors"

    Private Sub DisableControl()
        btnRun.Enabled = True
    End Sub

    Private Sub Import()
        Try
            'BackUpDatabase()
            If Not _Thread Is Nothing Then
                If _Thread.IsAlive Then
                    Exit Sub
                End If
            End If
            ClearAfterImportControls()
            Application.DoEvents()

            If Validations() Then
                _IsThreadAborted = False
                _SheetName = CType(cmbSheets.SelectedItem, DataRowView).Item("Name")
                _FormatID = cmbTemplate.SelectedValue
                SetContolsOnImportClick(True)
                gpInvestorImport.Enabled = False
                btnRun.Enabled = True
                btnClear.Enabled = False
                cmbSheets.Enabled = False
                _Thread = New Thread(AddressOf ImportData)
                _Thread.IsBackground = True
                _Thread.Start()
            End If

        Catch ex As Exception
            SetContolsOnImportClick(False)
            MsgBox(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in btnImport_Click of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally

            '  gpInvestorImport.Enabled = True
        End Try
    End Sub
    ''' <summary>
    ''' 'To Import the Sheet Data Once its format is seleted
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click
        Import()
    End Sub

    ''' <summary>
    ''' To check the mandetory controls data
    ''' </summary>
    ''' <returns>returns false if validation fails</returns>
    ''' <remarks></remarks>

    Private Function Validations() As Boolean
        Dim BoolCheck As Boolean = True
        If CommonCLL.IsEmpty(txtAttach.Text) Then
            MessageBox.Show("File name can not be left blank.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtAttach.Focus()
            BoolCheck = False
            Return BoolCheck
        End If
        If Not IO.File.Exists(Trim(txtAttach.Text)) Then
            MessageBox.Show("Please select a valid file name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtAttach.Focus()
            BoolCheck = False
            Return BoolCheck
        End If

        If CommonCLL.CheckEmptyDataTable(CType(cmbSheets.DataSource, DataTable)) Then
            MessageBox.Show("Please select a valid file name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            cmbSheets.Focus()
            BoolCheck = False
            Return BoolCheck
        Else
            If CType(cmbSheets.SelectedValue, DataRowView).Item("SNo.") <= 0 Then
                MessageBox.Show("Please select a sheet name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                cmbSheets.Focus()
                BoolCheck = False
                Return BoolCheck
            End If
        End If
        If cmbTemplate.SelectedValue <= 0 Then
            MessageBox.Show("Please select a format name.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            cmbTemplate.Focus()
            BoolCheck = False
            Return BoolCheck
        End If

        If BlnCheckMandatory() = False Then
            BoolCheck = False
            Return BoolCheck
        End If

        Return BoolCheck
    End Function

    ''' <summary>
    ''' To Check the Mandatory Fields,is that mapped Properly 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Private Function BlnCheckMandatory() As Boolean
        Dim ReturnVal As ExecutionResult
        Dim CImportinvestor As New cImportInvestors
        Try
            CImportinvestor.TemplateID = cmbTemplate.SelectedValue
            CImportinvestor.SourceTypeCode = SourceTypeCode.INV.ToString()
            ' m_iFormatID = cmbFormat.SelectedValue
            ReturnVal = CImportinvestor.ChkImportInvestorIsIsmandatory()
            If ReturnVal.ResultParamenters(2).Value = "" Then
                Return True
            Else
                MessageBox.Show("Please Map Manadatory field '" & ReturnVal.ResultParamenters(2).Value & "'", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in BlnCheckMandatory of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        Finally
            ReturnVal = Nothing
            CImportinvestor = Nothing
        End Try
        Return True

    End Function

    ''' <summary>
    ''' To set the controls on the click of Import button
    ''' </summary>
    ''' <param name="value"></param>
    ''' <remarks></remarks>
    Private Sub SetContolsOnImportClick(ByVal value As Boolean)
        Try
            If value Then
                btnClear.Enabled = False
                gpInvestorImport.Enabled = False
                btnRun.Enabled = False

            Else
                btnPause.Enabled = False
                btnContinue.Enabled = False
                btnCancel.Enabled = False
                btnClear.Enabled = True
                gpInvestorImport.Enabled = True
                lblInProgress.Visible = False
                btnClose.Enabled = True
            End If
        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in SetContolsOnImportClick of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try
    End Sub

    ''' <summary>
    ''' Checks the file consistancy
    ''' </summary>
    ''' <param name="pdtinvestor"></param>
    ''' <param name="excelClone"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ValidateFile(ByRef pdtinvestor As DataTable, ByVal excelClone As DataTable, ByRef listNewType As List(Of String)) As Boolean
        Dim strStyle As String = ""
        Dim ldtValidationTypes As DataTable = Nothing
        Dim ldrValidationType As DataRow = Nothing
        Dim dSet As New DataSet
        Dim lintColidex As Integer
        Dim Validatedtable As New DataTable
        Try
            'strEntityID = "EntityID"
            ldtValidationTypes = New DataTable
            ldtValidationTypes.Columns.Add("Message", GetType(Integer))
            ldtValidationTypes.Columns.Add("Validation Type", GetType(String))

            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            dSet = CInvestorDetails.GetInvestorValidatedData()
            Validatedtable = dSet.Tables(0)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            Invoke(New GenricDelegate(AddressOf CompleteProgressBar))

            If CommonCLL.CheckEmptyDataTable(Validatedtable) Then
                Return True
            End If

            lintColidex = 0
            For Each row As DataRow In Validatedtable.Rows
                PrepareValidationTypeList(row.Item("Message").ToString(), ldtValidationTypes)
            Next
            MessageBox.Show("Import process has been aborted due to the following inconsistencies", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
            If Validatedtable.Columns.Contains("InvEntityID") Then
                Validatedtable.Columns("InvEntityID").Caption = "Investor ID"
            End If
            If Validatedtable.Columns.Contains("InvestorName") Then
                Validatedtable.Columns("InvestorName").Caption = "Investor Name"
            End If
            If Validatedtable.Columns.Contains("EStatus") Then
                Validatedtable.Columns("EStatus").Caption = "Status"
            End If
            If Validatedtable.Columns.Contains("HeadInvestor") Then
                Validatedtable.Columns("HeadInvestor").Caption = "Head Investor"
            End If
            If Validatedtable.Columns.Contains("InvestorType") Then
                Validatedtable.Columns("InvestorType").Caption = "Investor Type"
            End If
            If Validatedtable.Columns.Contains("UltimateParent") Then
                Validatedtable.Columns("UltimateParent").Caption = "Ultimate Parent"
            End If
            If Validatedtable.Columns.Contains("MInvestorType") Then
                Validatedtable.Columns("MInvestorType").Caption = "Master Investor Type"
            End If
            If Validatedtable.Columns.Contains("ActivistCategory") Then
                Validatedtable.Columns("ActivistCategory").Caption = "Activist Category"
            End If
            If Validatedtable.Columns.Contains("LastReportDate") Then
                Validatedtable.Columns("LastReportDate").Caption = "Last Report Date"
            End If
            If Validatedtable.Columns.Contains("EquityAssets") Then
                Validatedtable.Columns("EquityAssets").Caption = "Equity Assets"
            End If
            If Validatedtable.Columns.Contains("CountryName") Then
                Validatedtable.Columns("CountryName").Caption = "Country Name"
            End If
            Validatedtable.Columns.Remove("EmailAddress")
            Validatedtable.Columns.Remove("Website")
            Validatedtable.Columns.Remove("Street1")
            Validatedtable.Columns.Remove("Street2")
            Validatedtable.Columns.Remove("MPhoneNumber")
            Validatedtable.Columns.Remove("TPhoneNumber")
            Validatedtable.Columns.Remove("TExtension")
            Validatedtable.Columns.Remove("FaxNumber1")
            Validatedtable.Columns.Remove("FaxNumber2")
            Validatedtable.Columns.Remove("PartRank")

            If Not CommonCLL.CheckEmptyDataTable(Validatedtable) Then
                Me.Invoke(New dlShowValidation(AddressOf ShowValidations), ldtValidationTypes, Validatedtable)
                Me.Invoke(New ControlVisiblity(AddressOf ShowNotImportedGrid), Validatedtable.Rows.Count > 0)
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), True)
                Me.Invoke(New GenricDelegate(AddressOf ClearAfterImportControls))
                InitProgressBar()
            End If

        Catch ex As Exception
            Me.FormulateAndLogError(ex, Me.Text, 0)
        Finally
            Validatedtable = Nothing
            ldtValidationTypes = Nothing
            ldrValidationType = Nothing
            dSet = Nothing
        End Try
        Return False
    End Function

    Private Sub ShowValidations(ByVal ldtValidationTypes, ByVal DuplicateRecord)
        Dim lfrmInvestorUploadValidations As New frmInvestorUploadValidations(ldtValidationTypes, DuplicateRecord)
        'lfrmInvestorUploadValidations.Location = "Investor"
        lfrmInvestorUploadValidations.WindowState = FormWindowState.Maximized
        AddHandler lfrmInvestorUploadValidations.ReRunEvent, AddressOf btnReRunClickEvent
        lfrmInvestorUploadValidations.Show()
        'lfrmInvestorUploadValidations.Focus()
        'lfrmInvestorUploadValidations.BringToFront()
        'lfrmInvestorUploadValidations.Select()
    End Sub

    Private Sub PrepareValidationTypeList(ByVal strMessage As String, ByRef ldtValidationTypes As DataTable)
        Dim FilterQuery As String
        Dim Excel() As DataRow
        Dim ldrValidationType As DataRow
        FilterQuery = "Message='" + Trim(strMessage).Replace("'", "''") + "'"
        Excel = ldtValidationTypes.Select(FilterQuery)
        If Excel.Length = 0 Then
            ldrValidationType = ldtValidationTypes.NewRow
            ldrValidationType.Item("Message") = strMessage
            ldrValidationType.Item("Validation Type") = GridClick(ldrValidationType.Item("Message"))
            ldtValidationTypes.Rows.Add(ldrValidationType)
        End If
    End Sub

    Public Sub VisiblityLabels(ByVal Val As Boolean)
        lblInProgress.Visible = Val

    End Sub
    Public Sub btnReRunClickEvent()
        btnReRun.PerformClick()
    End Sub

    Public Sub ButtonVisiblity(ByVal Val As Boolean)
        btnPause.Enabled = Val
        btnCancel.Enabled = Val
        lblInProgress.Text = " System is checking for file consistency. Please wait..."

    End Sub

    Public Sub LabelMessages(ByVal Val As Boolean)
        If Val = True Then
            lblInProgress.Text = " System is fetching data from excel file. Please wait..."
        Else
            lblInProgress.Text = " Please wait. Data Import process is in progress......"
        End If

    End Sub

    ''' <summary>
    ''' This will set the visibility of reimport button, which can be enabled after sytem catch the excel in validation
    ''' </summary>
    ''' <param name="Val"></param>
    ''' <remarks></remarks>
    ''' 
    Private Sub VisibilityReimportButton(ByVal Val As Boolean)
        btnReRun.Enabled = Val
        btnPause.Enabled = False
        btnCancel.Enabled = False
        If btnReRun.Enabled Or btnRun.Enabled Then
            lblInProgress.Visible = False
            btnClose.Enabled = True
        End If
    End Sub

    Private Sub VisiblityImportButton(ByVal val As Boolean)
        btnRun.Enabled = val
        btnClose.Enabled = val
        gpInvestorImport.Enabled = val
        cmbSourceName.Enabled = val
        cmbTemplate.Enabled = val
        btnTemplate.Enabled = val
        cmbSheets.Enabled = val
    End Sub
    Private Sub HideTableColumns(ByVal table As DataTable)
        If table.Columns.Contains("EStatus") Then
            table.Columns.Remove("EStatus")
        End If
    End Sub
    ''' <summary>
    ''' Function to call the Class's function that Import data in the database
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub ImportData()
        Dim Investor As New DataTable
        Dim ExcelClone As New DataTable
        Dim IsColumDupliacte As Boolean = False
        Dim LoadInvestor As GenricDelegate
        Dim deadlockerror As Boolean = False
        Dim listNewInvType As List(Of String) = Nothing
        Dim Logmessage As New System.Text.StringBuilder
        Dim datareader As IDataReader
        Try
            Me.Invoke(New ControlVisiblity(AddressOf VisiblityLabels), True)
            Me.Invoke(New ControlVisiblity(AddressOf LabelMessages), True)
            Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), False)
            'Application.DoEvents()
            InitProgressBar()
            Thread.Sleep(3000)
            Me.Invoke(New ControlVisiblity(AddressOf LabelMessages), False)
            Me.Invoke(New ControlVisiblity(AddressOf ButtonVisiblity), True)
            'IncrementProgressBar()
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 3)
            If Not GetExcelFileData(IsColumDupliacte, False, Investor, txtAttach.Text, _SheetName, _TotalExcelRows) Then
                If IsColumDupliacte = False Then
                    MessageBox.Show("Please select correct file to import investor data.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                    Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                    '***********************************************
                End If
                ProgressBarCompleteStart()
                Exit Sub
            End If
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 17)

            If Not MapWithDatabaseColumns(_FormatID, Investor, ExcelClone) Then
                SetCursorandLabelToNormal()
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityLabels), False)
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), True)
                ProgressBarCompleteStart()

                '**************************************************
                Exit Sub
            End If

            If Not IsFieldsMapped(ExcelClone, _FormatID) Then
                Me.Invoke(New ControlVisiblity(AddressOf VisiblityImportButton), True)
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                ProgressBarCompleteStart()

                '**********************************************
                Exit Sub
            End If

            Dim dtTemplateFields As DataTable = Nothing
            CInvestorDetails.ClearBulkInvestorImport("INV_BulkImportValidation")
            ResetOrdinal(Investor)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 15)
            Dim sqlBulkCopy1 As New SqlClient.SqlBulkCopy(CommonCLL.mstrConnectionstring)
            sqlBulkCopy1.DestinationTableName = "INV_BulkImportValidation"
            For i As Integer = 0 To Investor.Columns.Count - 1 Step 1
                sqlBulkCopy1.ColumnMappings.Add(i, i + 1)
            Next
            sqlBulkCopy1.WriteToServer(Investor)
            sqlBulkCopy1.Close()
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 10)

            If Not ValidateFile(Investor, ExcelClone, listNewInvType) Then Exit Sub
            BackUpDatabase()

            InitProgressBar()
            Thread.Sleep(3000)
            Me.Invoke(New ControlVisiblity(AddressOf ButtonVisiblity), True)
            Me.Invoke(New ControlVisiblity(AddressOf LabelMessages), False)
            Me.Invoke(New IncrementBySteps(AddressOf IncrementProgressBarSteps), 15)

            Dim dSet As New DataSet
            dSet = CInvestorDetails.GetValidatedData()
            Investor = dSet.Tables(0)
            CInvestorDetails.ClearBulkInvestorImport("INV_BulkInvestorImport")
            datareader = Investor.CreateDataReader()
            Dim sqlBulkCopy As New SqlClient.SqlBulkCopy(CommonCLL.mstrConnectionstring)
            sqlBulkCopy.DestinationTableName = "INV_BulkInvestorImport"
            Dim validator As Crystal.CLL.ValidatingDataReader
            validator = New Crystal.CLL.ValidatingDataReader(datareader, New SqlConnection(CommonCLL.mstrConnectionstring), sqlBulkCopy)
            sqlBulkCopy.WriteToServer(validator)
            'sqlBulkCopy.WriteToServer(Investor)
            validator = Nothing
            datareader.Dispose()
            sqlBulkCopy.Close()

            If CInvestorDetails.ImportInvestors(Investor) Then
                Me.Invoke(New ControlVisiblity(AddressOf VisibilityReimportButton), False)
                Me.Invoke(New ControlVisiblity(AddressOf SetContolsOnImportClick), False)

                '************************************************
                Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
                Invoke(New ShowState(AddressOf ImportedDataDetails))
                Application.DoEvents()
                'LoadInvestor = New GenricDelegate(AddressOf LoadRegionForm)
                'Me.BeginInvoke(LoadInvestor)
                RaiseEvent LoadImportedInvestor(Investor)
                '''''Logging
                Logmessage.Append("Format ID - ")
                Logmessage.Append(CType(_FormatID, String))
                Logmessage.Append(", Sheet Name - ")
                Logmessage.Append(_SheetName)
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Investor import completed successfully.")
                LogErrorToDatabase(Logmessage.ToString())
                'CreateErrorLog(Logmessage)
                ''''''''''''''''''''''''''''
            Else
                Application.DoEvents()
            End If
        Catch ex As Exception
            Application.DoEvents()
            '''''Logging
            Logmessage.Append("Format ID - ")
            Logmessage.Append(CType(_FormatID, String))
            Logmessage.Append(", Sheet Name - ")
            Logmessage.Append(_SheetName)
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Investor import can not be completed successfully.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.Message.ToString())
            CreateErrorLog(Logmessage)
            ''''''''''''''''''''''''''''
            If (ex.Message.ToString().Substring(ex.Message.ToString().LastIndexOf(" ")).Trim() = "1205") Then
                deadlockerror = True
            End If
            If _IsThreadAborted = False Then
                If (deadlockerror) Then
                    MessageBox.Show(ex.Message.Substring(0, ex.Message.ToString().LastIndexOf(" ")), m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    MessageBox.Show(ex.Message, m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
                'If (ex.Message = "There are some investors which have same multiple ultimate parent in excel sheet. Please correct the excel sheet and try again.") Then
                '   Else If()
                '   MessageBox.Show(ex.Message)
            End If

            '  End If
        Finally

            Investor = Nothing
            ExcelClone = Nothing
            'clscInvestorDetails = Nothing
            LoadInvestor = Nothing
            If _IsThreadAborted = False Then
                ' Me.Invoke(New ControlVisiblity(AddressOf SetContolsOnImportClick), False)
                If (deadlockerror) Then
                    Me.Invoke(New GenricDelegate(AddressOf DisableControl))
                End If
            End If
            datareader = Nothing
        End Try


    End Sub
    Private Sub ProgressBarCompleteStart()
        Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
        InitProgressBar()
    End Sub
    Private Sub ResetOrdinal(ByVal Investor As DataTable)
        Dim dtInvestorClone As DataTable = Investor.Clone()
        Dim intCounter As Integer
        Dim IsCounterChanged As Boolean = False
        If dtInvestorClone.Columns(0).ColumnName <> "InvEntityID" Then
            Investor.Columns("InvEntityID").SetOrdinal(0)
        End If
        If dtInvestorClone.Columns(1).ColumnName <> "InvestorName" Then
            Investor.Columns("InvestorName").SetOrdinal(1)
        End If
        If dtInvestorClone.Columns(2).ColumnName <> "Status" Then
            Investor.Columns("Status").SetOrdinal(2)
        End If
        If Investor.Columns.Contains("Website") = False Then
            Investor.Columns.Add("Website", GetType(String))
        End If
        Investor.Columns("Website").SetOrdinal(3)

        If dtInvestorClone.Columns(4).ColumnName <> "HeadInvestor" Then
            Investor.Columns("HeadInvestor").SetOrdinal(4)
        End If
        If dtInvestorClone.Columns(5).ColumnName <> "Type" Then
            Investor.Columns("Type").SetOrdinal(5)
        End If
        If dtInvestorClone.Columns(6).ColumnName <> "Style" Then
            Investor.Columns("Style").SetOrdinal(6)
        End If
        If dtInvestorClone.Columns(7).ColumnName <> "Turnover" Then
            Investor.Columns("Turnover").SetOrdinal(7)
        End If
        If dtInvestorClone.Columns(8).ColumnName <> "Country" Then
            Investor.Columns("Country").SetOrdinal(8)
        End If
        If dtInvestorClone.Columns(9).ColumnName <> "City" Then
            Investor.Columns("City").SetOrdinal(9)
        End If
        If dtInvestorClone.Columns(10).ColumnName <> "State" Then
            Investor.Columns("State").SetOrdinal(10)
        End If

        If IsCounterChanged Then
            intCounter = 0
            IsCounterChanged = False
        End If
        dtInvestorClone = Investor.Clone()
        If Investor.Columns.Contains("Parent") = False Then
            Investor.Columns.Add("Parent", GetType(String))
        End If
        Investor.Columns("Parent").SetOrdinal(11)
        If dtInvestorClone.Columns.Contains("UltimateParent") = False Then
            Investor.Columns.Add("UltimateParent", GetType(String))
        End If
        Investor.Columns("UltimateParent").SetOrdinal(12)

        If dtInvestorClone.Columns.Contains("MInvestorType") = False Then
            Investor.Columns.Add("MInvestorType", GetType(String))
        End If
        Investor.Columns("MInvestorType").SetOrdinal(13)

        If Investor.Columns.Contains("Activist") = False Then
            Investor.Columns.Add("Activist", GetType(String))
        End If
        Investor.Columns("Activist").SetOrdinal(14)

        If Investor.Columns.Contains("LastReportDate") = False Then
            Investor.Columns.Add("LastReportDate", GetType(String))
        End If
        Investor.Columns("LastReportDate").SetOrdinal(15)

        If Investor.Columns.Contains("EquityAssets") = False Then
            Investor.Columns.Add("EquityAssets", GetType(String))
        End If
        Investor.Columns("EquityAssets").SetOrdinal(16)

        If Investor.Columns.Contains("Street1") = False Then
            Investor.Columns.Add("Street1", GetType(String))
        End If
        Investor.Columns("Street1").SetOrdinal(17)

        If Investor.Columns.Contains("Street2") = False Then
            Investor.Columns.Add("Street2", GetType(String))
        End If
        Investor.Columns("Street1").SetOrdinal(18)

        If Investor.Columns.Contains("Mobile") = False Then
            Investor.Columns.Add("Mobile", GetType(String))
        End If
        Investor.Columns("Mobile").SetOrdinal(19)

        If Investor.Columns.Contains("Telephone") = False Then
            Investor.Columns.Add("Telephone", GetType(String))
        End If
        Investor.Columns("Telephone").SetOrdinal(20)

        If Investor.Columns.Contains("Extension") = False Then
            Investor.Columns.Add("Extension", GetType(String))
        End If
        Investor.Columns("Extension").SetOrdinal(21)

        If Investor.Columns.Contains("Fax") = False Then
            Investor.Columns.Add("Fax", GetType(String))
        End If
        Investor.Columns("Fax").SetOrdinal(22)

        If Investor.Columns.Contains("Fax2") = False Then
            Investor.Columns.Add("Fax2", GetType(String))
        End If
        Investor.Columns("Fax2").SetOrdinal(23)

        If Investor.Columns.Contains("Email") = False Then
            Investor.Columns.Add("Email", GetType(String))
        End If
        Investor.Columns("Email").SetOrdinal(24)
        If Investor.Columns.Contains("EStatusID") = False Then
            Investor.Columns.Add("EStatusID", GetType(Integer))
        End If
        Investor.Columns("EStatusID").SetOrdinal(25)

        If Investor.Columns.Contains("HeadInvestorID") = False Then
            Investor.Columns.Add("HeadInvestorID", GetType(Integer))
        End If
        Investor.Columns("HeadInvestorID").SetOrdinal(26)

        If Investor.Columns.Contains("TypeID") = False Then
            Investor.Columns.Add("TypeID", GetType(Integer))
        End If
        Investor.Columns("TypeID").SetOrdinal(27)

        If Investor.Columns.Contains("StyleID") = False Then
            Investor.Columns.Add("StyleID", GetType(Integer))
        End If
        Investor.Columns("StyleID").SetOrdinal(28)

        If Investor.Columns.Contains("TurnoverLabel") = False Then
            Investor.Columns.Add("TurnoverLabel", GetType(Integer))
        End If
        Investor.Columns("TurnoverLabel").SetOrdinal(29)

        If Investor.Columns.Contains("CountryID") = False Then
            Investor.Columns.Add("CountryID", GetType(Integer))
        End If
        Investor.Columns("CountryID").SetOrdinal(30)

        If Investor.Columns.Contains("ParentID") = False Then
            Investor.Columns.Add("ParentID", GetType(Integer))
        End If
        Investor.Columns("ParentID").SetOrdinal(31)

        If Investor.Columns.Contains("UltimateParentID") = False Then
            Investor.Columns.Add("UltimateParentID", GetType(Integer))
        End If
        Investor.Columns("UltimateParentID").SetOrdinal(32)

        If Investor.Columns.Contains("MInvestorTypeID") = False Then
            Investor.Columns.Add("MInvestorTypeID", GetType(Integer))
        End If
        Investor.Columns("MInvestorTypeID").SetOrdinal(33)

        If Investor.Columns.Contains("ActivistID") = False Then
            Investor.Columns.Add("ActivistID", GetType(Integer))
        End If
        Investor.Columns("ActivistID").SetOrdinal(34)

        If Investor.Columns.Contains("CityID") = False Then
            Investor.Columns.Add("CityID", GetType(Integer))
        End If
        Investor.Columns("CityID").SetOrdinal(35)

        If Investor.Columns.Contains("StateID") = False Then
            Investor.Columns.Add("StateID", GetType(Integer))
        End If
        Investor.Columns("StateID").SetOrdinal(36)

        If Investor.Columns.Contains("InvestorID") = False Then
            Investor.Columns.Add("InvestorID", GetType(Integer))
        End If
        Investor.Columns("InvestorID").SetOrdinal(37)

        If Investor.Columns.Contains("RowNumber") = False Then
            Investor.Columns.Add("RowNumber", GetType(Integer))
        End If
        Investor.Columns("RowNumber").SetOrdinal(38)

        If Investor.Columns.Contains("PostalCode") = False Then
            Investor.Columns.Add("PostalCode", GetType(String))
        End If
        Investor.Columns("PostalCode").SetOrdinal(39)
        dtInvestorClone = Nothing
    End Sub

    ''' <summary>
    ''' Loads New Region Screen
    ''' </summary>
    ''' <remarks></remarks>

    Public Sub LoadRegionForm()

        'Dim FrmAddRegion As New frmAddRegion
        'Dim CInvestorDetails As New cInvestorDetails
        'Try
        '    If CInvestorDetails.IsNullRegionCountryExists() Then
        '        With FrmAddRegion
        '            .StartPosition = FormStartPosition.CenterScreen
        '            .ShowDialog()
        '        End With
        '    End If
        'Catch ex As Exception
        '    '''''Logging
        '    Dim Logmessage As New System.Text.StringBuilder
        '    Logmessage.Append("There is error in LoadRegionForm of form import investor.")
        '    Logmessage.Append(Environment.NewLine)
        '    Logmessage.Append("Error Message - ")
        '    Logmessage.Append(ex.ToString())
        '    CreateErrorLog(Logmessage)
        '    Logmessage = Nothing
        '    ''''''''''''''''''''''''''''
        'Finally
        '    CInvestorDetails = Nothing
        '    FrmAddRegion.Dispose()
        '    FrmAddRegion = Nothing
        'End Try

    End Sub

    ''' <summary>
    ''' sets the positio of the grid dynamically
    ''' </summary>
    ''' <param name="IsShow"></param>
    ''' <remarks></remarks>

    Private Sub ShowNotImportedGrid(ByVal isShow As Boolean)
        If isShow = True Then
            'Me.Height = _MeHeight
            'grdNotImported.Visible = True
            'lblNotImported.Visible = True
            'Me.Height = _MeHeight - gpbStats.Height - 10
            'lblReseaon.Visible = True
            ' grdNotImported.Location = New System.Drawing.Point(12, gpInvestorImport.Height + btnContinue.Height + 60)
            'lblNotImported.Location = New System.Drawing.Point(gpbStats.Location.X, gpbStats.Location.Y + 15)
            'lblReseaon.Location = New System.Drawing.Point(gpbStats.Location.X, gpbStats.Location.Y)
            'Me.WindowState = FormWindowState.Maximized
            btnReRun.Enabled = True
        Else
            'grdNotImported.Visible = False
            'lblNotImported.Visible = False
            'lblReseaon.Visible = False
        End If
    End Sub

    ''' <summary>
    ''' To Set Messages Accordingly
    ''' </summary>
    ''' <param name="isImport"></param>
    ''' <remarks></remarks>

    Private Sub SetLabelText(ByVal isImport As Boolean)
        If isImport Then
            lblInProgress.Visible = True
            lblInProgress.Text = "Please wait. Data Import process is in progress....."
        Else
            lblInProgress.Text = "Please wait. Sheets are being loaded....."
        End If
    End Sub

    ''' <summary>
    '''  To Show the End Result of Import Process
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub ImportedDataDetails()
        MessageBox.Show("Data imported successfully.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
        lblInProgress.Visible = False
        btnReRun.Enabled = False
        gpInvestorImport.Enabled = False
        btnClear.Enabled = False
        btnClose.Text = "&OK"
    End Sub

    ''' <summary>
    ''' 'To Initialize the Progressbar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub InitProgressBar()
        If pgbInvestor.InvokeRequired = False Then
            With pgbInvestor
                .Value = 0
                .Step = 1
                .Maximum = 100 ' dtExcel.Rows.Count - 1
                .Show()
            End With
        Else
            Me.pgbInvestor.Invoke(New GenricDelegate(AddressOf InitProgressBar))
        End If

    End Sub

    ''' <summary>
    '''   'Incrementing the Progress Bar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub IncrementProgressBar() 'Handles ObjcInvestorDetails.entInvRecordAdded
        pgbInvestor.IncrementValue(pgbInvestor.Step)
        Application.DoEvents()
    End Sub

    Private Sub IncrementProgressBarSteps(ByVal pintCount As Integer)
        Dim lintStartValue As Integer = pgbInvestor.Value
        Dim lintCounter As Integer = lintStartValue
        While lintCounter <= lintStartValue + pintCount
            pgbInvestor.IncrementValue(pgbInvestor.Step)
            lintCounter += 1
        End While
        Application.DoEvents()
    End Sub

    Private Sub IncrementProgressBarValue() Handles CInvestorDetails.EntInvRecordAdded
        If Me.pgbInvestor.InvokeRequired = False Then
            pgbInvestor.IncrementValue(ProgressBar.StepValue)
            Application.DoEvents()
        Else
            Me.pgbInvestor.Invoke(New GenricDelegate(AddressOf IncrementProgressBarValue))
        End If
    End Sub

    ''' <summary>
    '''  'Completing the ProgressBar
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub CompleteProgressBar()
        If pgbInvestor.InvokeRequired = False Then
            While pgbInvestor.Value < pgbInvestor.Maximum
                IncrementProgressBar()
            End While
        Else
            Me.pgbInvestor.Invoke(New GenricDelegate(AddressOf CompleteProgressBar))
        End If

    End Sub

    ''' <summary>
    ''' 'Set the Cursor Status
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub SetCursorandLabelToNormal()
        Me.Cursor = Cursors.Default
        lblInProgress.Visible = False
    End Sub

    Private Sub SetControlsAfterImport()

        ' Me.Height = _MeHeightWithStat
        'gpbStats.Visible = True
        lblInProgress.Visible = False
    End Sub

#End Region

#Region "RaiseEvents"

    Public Sub Reload(ByVal formatid As Integer) Handles _InvestorFormat.ReloadFormatId
        LoadTemplate()
        cmbTemplate.SelectedValue = formatid
    End Sub

#End Region

#Region "Sheet's Selected Index Changed"

    ''' <summary>
    '''  'To Get Excel Sheet Data
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>

    Private Sub cmbSheets_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSheets.SelectedIndexChanged
        If _IsReRun Then Exit Sub
        Dim IsColumDupliacte As Boolean = False
        Try
            ClearAfterImportControls()
            _IsChkSheet = False
            cmbSourceName.Enabled = False
            cmbTemplate.Enabled = False
            btnSourceName.Enabled = False
            btnTemplate.Enabled = False
            If Not cmbTemplate.SelectedIndex > 0 Then
                btnRun.Enabled = False
            End If
            If cmbSheets.SelectedIndex > 0 Then
                lblInProgress.Text = "Please Wait!"
                cmbSourceName.Enabled = True
                cmbTemplate.Enabled = True
                btnSourceName.Enabled = True
                btnTemplate.Enabled = True
                If cmbSourceName.SelectedIndex <> -1 Then
                    If DirectCast(cmbSourceName.DataSource, DataTable).Rows.Count > 0 Then
                        cmbSourceName.SelectedIndex = 1
                    Else
                        cmbSourceName.SelectedIndex = 0
                    End If
                End If
                If cmbTemplate.SelectedIndex <> -1 Then
                    If DirectCast(cmbTemplate.DataSource, DataTable).Rows.Count > 1 Then
                        cmbTemplate.SelectedIndex = 1
                    Else
                        cmbTemplate.SelectedIndex = 0
                    End If
                End If
                _IsChkSheet = True
                If cmbTemplate.SelectedIndex > 0 Then
                    btnRun.Enabled = True
                End If
                lblInProgress.Text = ""
            Else
                btnRun.Enabled = False
                cmbSourceName.SelectedIndex = 0
                cmbTemplate.SelectedIndex = -1

            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbSheets_SelectedIndexChanged of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try

    End Sub

#End Region

#Region "Method To Clear all the Fields"

    Private Sub ClearAllFields()
        If Not cmbSheets.SelectedValue Is Nothing Then
            cmbSheets.SelectedIndex = 0
        End If
        If Not cmbSourceName.SelectedValue Is Nothing Then
            cmbSourceName.SelectedIndex = 0
        End If
        If Not cmbTemplate.SelectedValue Is Nothing Then
            cmbTemplate.SelectedIndex = 0
        End If
        cmbSheets.Enabled = False
        cmbTemplate.Enabled = False
        btnTemplate.Enabled = False

        cmbSheets.Enabled = False
        txtAttach.Text = ""
        ClearAfterImportControls()
        _IsReRun = False
    End Sub

    Private Sub ClearAfterImportControls()
        pgbInvestor.Value = 0
        'gpbStats.Visible = False
        'statInvestors.Text = ""
        'grdNotImported.Visible = False
        lblInProgress.Visible = False
        'statInvestors.Panels(0).Text = ""
        ' Me.Height = HerightOnload + 30
        'lblNotImported.Visible = False
        'grdNotImported.Visible = False
        'gpbStats.Visible = False
        'lblReseaon.Text = ""
        'pgbInvestor.Value = 0

        'lblTotalImportedV.Text = 0
        'lblTotalInsertedV.Text = 0
        'lblTotalUpdatedV.Text = 0
    End Sub

#End Region

#Region "Format's Selected Index Changed"

    ''' <summary>
    ''' 'To Check the Format is Selected or Not
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub cmbFormat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbTemplate.SelectedIndexChanged
        Try
            btnRun.Enabled = False
            If cmbTemplate.SelectedIndex > 0 Then
                btnClear.Enabled = True
                If _IsChkSheet = True Then
                    btnRun.Enabled = True
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in cmbFormat_SelectedIndexChanged of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
        End Try

    End Sub

#End Region

#Region "BackUp"

    Private Sub BackUpDatabase()
        Dim srv As Server
        'Reference the AdventureWorks database.
        Dim db As Database
        'Store the current recovery model in a variable.
        'Define a Backup object variable. 
        Dim connString As String = CommonCLL.mstrConnectionstring
        Dim connection As SqlConnection = New SqlConnection(connString)
        Dim database As String = connection.Database.ToString()
        Dim bk As New Backup
        Dim name As String = String.Empty
        Dim backupdate As New Date
        Try
            'Specify the type of backup, the description, the name, and the database to be backed up.
            srv = New Server
            db = srv.Databases("AdventureWorks2008R2")
            bk.Action = BackupActionType.Database
            bk.BackupSetDescription = "Full backup of Crystal"
            bk.BackupSetName = "Crystal Backup"
            bk.Database = database
            'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
            name = DateTime.Now.ToString()
            name = name.Replace(":", "_").Replace("/", "_")
            Dim bdi As BackupDeviceItem
            bdi = New BackupDeviceItem(database + name + ".bak", DeviceType.File)
            'Add the device to the Backup object.
            bk.Devices.Add(bdi)
            'Set the Incremental property to False to specify that this is a full database backup.
            bk.Incremental = False
            'Set the expiration date.
            backupdate = New Date(2012, 10, 5)
            bk.ExpirationDate = backupdate
            'Specify that the log must be truncated after the backup is complete.
            bk.LogTruncation = BackupTruncateLogType.Truncate
            'Run SqlBackup to perform the full database backup on the instance of SQL Server.
            bk.SqlBackup(srv)
            ShowMessageBox(DBResult.DATABASE_BACKUP_CREATED)
            'Inform the user that the backup has been completed.
            'Remove the backup files from the hard disk.
            'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Full_Backup1")
            'My.Computer.FileSystem.DeleteFile("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test_Differential_Backup1")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            connection = Nothing
            database = String.Empty
            bk = Nothing
        End Try
    End Sub

#End Region
End Class


